Import Engine Formula Use Cases

Task/Problem Overview

The Inventory Import tool allows you to create and update part numbers with new information, such as pricing and descriptions. It allows you to map columns from a spreadsheet or delimited text file to fields built into the Savance Enterprise software. There are many instances where the data in the file needs to be transformed before it can be committed into the software’s database. This is done through the use of formulas. This document will run through formula examples that are used for common scenarios that arise.

How To Input A Formula

Go to Admin > Import > Inventory Import.

You will need to choose a source file and template before being able to do anything. Then you will be able to see all the columns.

To edit a formula, click on the … at the end of the value expression for the corresponding field name. The Import Formula Editor will be displayed. Input your formula here. You can drag import inputs and existing values from the left to the formula editor box. To test your formula, you can input a test value on the right, hit the Test button, and see what the formula will do to your input in the lower right. In the following example, the formula is going to take the UPC from the import file, and only import the first 11 digits. So, a 12 digit UPC in the file will be truncated to 11 digits. Here is what the formula looks like, along with the test value and expected output.

Formula Examples

This is the most basic form of a formula. It is indicating that the ManufacturerPartNumber column in the spreadsheet needs no data transformations and can be imported as it is.

Output = {ManufacturerPartNumber}

In the following example, the formula is going to take the UPC from the import file, and only import the first 11 digits. So, a 12 digit UPC in the file will be truncated to 11 digits. Here is what the formula looks like.

Output = LEFT({UPC}, 11)

Here is a simple If Then Else formula. This is useful when the data in the file needs to be changed to something else, depending on what it is. In the following example, the Units column in the file contains the numbers 1, 100, and 1000. This should be imported into the software as E, C, and M.

If {Units} = "100" Then

Output = "C"

Else If {Units} = "1000" Then

Output = "M"

Else

Output = "E"

End If

Here is another example of an If-Then-Else formula. In this case, the ObsoleteStatus column in the file contains letters and needs to be transformed into numbers. It requires some knowledge of what the letters stand for in the file, and what the numbers stand for in the software. O stands for “obsolete”, and P stands for “planned obsolescence”. If you query the ObsoleteStatuses table in the database, you will see that 0 stands for “Not obsolete”, 1 stands for “Obsolete and cannot be used”, and 2 stands for “Delete when the stock reaches 0”. We will map the data accordingly.

If {ObsoleteStatus} = "O" Then

Output = 1

Else If {ObsoleteStatus} = "P" Then

Output = 2

Else

Output = 0

End If

You can also import data in the file from a different column, based on the contents of another column. In this example, if the Cost column in the file is blank, then it will use the data from the StandardCost column instead. Otherwise, it will use whatever is in the Cost column. This is to protect against importing a blank cost into your inventory. However, this formula assumes that StandardCost is never blank, so that is something that needs to be taken into consideration. It is best to have a good understanding of what is inside your data file so that you can account for scenarios such as this.

If {Cost} = "" Then

Output = {StandardCost}

Else

Output = {Cost}

End If

Sometimes when doing an import, you want to update something only in the case that it is for a new item. For example, the Descriptions for your existing inventory may be very accurate, and you do not want them to be changed. However, if there is a new item in the file that is to be created, you will want to use the description in the file so that you have something. This is where this type of formula comes in handy. It is based on the If -Then-Else formula. If the part number is new to your inventory, then you are telling it to use the Description in the import file. Otherwise, the part number already exists, and since you want to keep your existing description, you are telling it to skip updating the description for this particular part.

If ExistingColumns("IsNew") Then

Output = {Description}

Else

Output = SkipUpdating()

End If

Sometimes, the data in the file is expected to be numeric. However, the column may contain erroneous alphanumeric data. When this happens, you do not want to import this data because the expected output is a number. This formula will check if a field in the file is numeric. If it is, it will import it. If not, it will be skipped.

If IsNumeric({PKG_LENGTH}) Then

Output = {PKG_LENGTH}

Else

Output = SkipUpdating()

End If

Sometimes, the UPC is invalid and you only want to update if the UPC is valid. Here is an example of how to accomplish that with the SkipUpdating command.

If LEN({UPC}) >=11 and LEN({UPC}) <= 13 then

OUTPUT = {UPC}

ELSE

OUTPUT = SkipUpdating()

END IF

In Conclusion

The elements from these formulas can be mixed and matched to accomplish just about anything, depending on what your needs are. The possibilities are endless. If there are any scenarios that you can think of that aren’t included here, please let us know, and we can get it added.